﻿using Model;
using System;
using System.Collections.Generic;
using System.Data;

namespace BLL
{
    public class Sys_OptinionBLL : BaseBLL
    {
        /// <summary>
        /// 新增意见处理
        /// </summary>
        /// <param name="entity">实体</param>
        /// <param name="operatename">用户名称</param>
        /// <returns></returns>
        public int Insert(Sys_Optinion entity, string operatename)
        {
            entity.N_OPIN_ID = GetSequence("SYS_OPTINION");
            var sql = string.Format(@"INSERT INTO SYS_OPINION(
	N_OPIN_ID,
	C_OPIN_NAME,
	N_STATE,
	N_ISDEL,
	N_USER_ID,
	D_OPIN_TIME
)
VALUES(
	{0},
	'{1}',
	{2},
	{3},
	{4},
	TO_DATE('{5}', 'YYYY-MM-DD HH24:MI:SS')
)",
  entity.N_OPIN_ID,
  Transference(entity.C_OPIN_NAME),
  entity.N_STATE,
  entity.N_ISDEL,
  entity.N_USER_ID,
  entity.D_OPIN_TIME.ToString("yyyy-MM-dd HH:mm:ss"));
            return NoQuery(sql, operatename);
        }

        /// <summary>
        /// 修改意见处理
        /// </summary>
        /// <param name="entity">实体</param>
        /// <param name="operatename">用户名称</param>
        /// <returns></returns>
        public int Update(Sys_Optinion entity, string operatename)
        {
            var sql = string.Format(@"UPDATE SYS_OPINION
SET
	C_OPIN_NAME = '{1}',
	N_STATE = {2},
	N_ISDEL = {3},
	N_USER_ID = {4},
	D_OPIN_TIME = TO_DATE('{5}', 'YYYY-MM-DD HH24:MI:SS')
WHERE
	N_OPIN_ID = {0}",
  entity.N_OPIN_ID,
  Transference(entity.C_OPIN_NAME),
  entity.N_STATE,
  entity.N_ISDEL,
  entity.N_USER_ID,
  entity.D_OPIN_TIME.ToString("yyyy-MM-dd HH:mm:ss"));
            return NoQuery(sql, operatename);
        }

        /// <summary>
        /// 删除一条意见处理
        /// </summary>
        /// <param name="id">ID</param>
        /// <param name="operatename">用户名称</param>
        /// <returns></returns>
        public int Delete(decimal id, string operatename)
        {
            //var sql = string.Format(@"DELETE FROM SYS_OPINION WHERE N_OPIN_ID = {0}", id);
            var sql = string.Format(@"UPDATE SYS_OPINION SET N_ISDEL = 1 WHERE N_OPIN_ID = {0}", id);
            return NoQuery(sql, operatename);
        }

        /// <summary>
        /// 批量删除意见处理
        /// </summary>
        /// <param name="ids"></param>
        /// <param name="operatename">用户名称</param>
        /// <returns></returns>
        public int DeleteRange(string ids, string operatename)
        {
            if (string.IsNullOrEmpty(ids))
            {
                return 0;
            }
            //var sql = string.Format(@"DELETE FROM SYS_OPINION WHERE N_OPIN_ID IN ({0})", ids);
            var sql = string.Format(@"UPDATE SYS_OPINION SET N_ISDEL = 1 WHERE N_OPIN_ID IN ({0})", ids);
            return NoQuery(sql, operatename);
        }

        public int DeleteRange(List<decimal> ids, string operatename)
        {
            if (ids.Count == 0)
            {
                return 0;
            }
            if (ids.Count == 1)
            {
                return Delete(ids[0], operatename);
            }
            var idsStr = "";
            for (int i = 1; i <= ids.Count; i++)
            {
                idsStr += ids[i - 1].ToString().Trim();
                if (i != ids.Count)
                {
                    idsStr += ",";
                }
            }
            return DeleteRange(idsStr, operatename);
        }

        /// <summary>
        /// 通过ID查询单条记录
        /// </summary>
        /// <param name="id">ID</param>
        /// <returns></returns>
        public Sys_Optinion Find(decimal id)
        {
            var sql = string.Format(@"SELECT
			N_OPIN_ID,
			C_OPIN_NAME,
			N_STATE,
			N_ISDEL,
			N_USER_ID,
			D_OPIN_TIME
		FROM
			SYS_OPINION
        WHERE
            N_OPIN_ID = {0}", id);
            var dt = Query(sql);
            if (dt.Rows.Count == 0)
            {
                return null;
            }
            var entity = new Sys_Optinion
            {
                N_OPIN_ID = Convert.ToDecimal(dt.Rows[0]["N_OPIN_ID"]),
                C_OPIN_NAME = Convert.ToString(dt.Rows[0]["C_OPIN_NAME"]),
                N_STATE = Convert.ToDecimal(dt.Rows[0]["N_STATE"]),
                N_ISDEL = Convert.ToDecimal(dt.Rows[0]["N_ISDEL"]),
                N_USER_ID = Convert.ToDecimal(dt.Rows[0]["N_USER_ID"]),
                D_OPIN_TIME = Convert.ToDateTime(dt.Rows[0]["D_OPIN_TIME"])
            };
            return entity;
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="userid">用户ID</param>
        /// <param name="skip">跳过行</param>
        /// <param name="take">选择行</param>
        /// <param name="count">返回总数量</param>
        /// <returns></returns>
        public DataTable GetPage(decimal userid, int skip, int take, string strsql, out int count)
        {
            var sql = @"SELECT n_opin_id,
       c_opin_name,
       n_state,
       n_isdel,
       n_user_id,
       to_char(d_opin_time, 'yyyy-mm-dd HH24:MI:SS') d_opin_time
  FROM SYS_OPINION WHERE N_ISDEL = 0 AND N_USER_ID = {0} " + strsql + " ORDER BY D_OPIN_TIME DESC";
            sql = string.Format(sql, userid);
            count = SqlCount(sql);
            return PageQuery(sql, skip, take);
            /*
            sql=string.Format(@"SELECT
	                                *
                                FROM
	                                (
                                        SELECT
                                            T1.*,
                                            ROWNUM TROW
                                        FROM
		                                (
                                            SELECT
			                                    N_OPIN_ID,
			                                    C_OPIN_NAME,
			                                    N_STATE,
			                                    N_ISDEL,
			                                    N_USER_ID,
			                                    D_OPIN_TIME
		                                    FROM
			                                    SYS_OPINION
                                            WHERE
                                                N_ISDEL = 0 AND
                                                N_USER_ID = {2}
		                                    ORDER BY
			                                    D_OPIN_TIME DESC) T1
	                                ) T1
                                WHERE
	                                T1.TROW > {0}
                                AND T1.TROW <= {1}", skip,skip+take,userid);
            return Query(sql);
            */
        }

        public DataTable GetSelect(decimal userid)
        {
            var sql = @"SELECT n_opin_id,
       c_opin_name,
       n_state,
       n_isdel,
       n_user_id,
       to_char(d_opin_time, 'yyyy-mm-dd HH24:MI:SS') d_opin_time
  FROM SYS_OPINION WHERE N_ISDEL = 0 AND N_USER_ID = {0} ORDER BY D_OPIN_TIME DESC";
            sql = string.Format(sql, userid);
            DataTable dt = Query(sql);
            return dt;
        }

        public DataTable BindData(string uid)
        {
            string sql = @"select t.n_opin_id,
       t.c_opin_name,
       t.n_state,
       t.n_isdel,
       n_user_id,
       to_char(t.d_opin_time, 'yyyy-mm-dd HH24:MI:SS') d_opin_time
  from sys_opinion t
 where t.n_state = 0
   and N_ISDEL = 0
   and t.N_USER_ID =" + uid;

            return Query(sql);
        }
    }
}